

#assign rating code and
#take average of ratings across different rating agencies for each rating_date
DT_bondratings[,rating_code:=0]
#assign code to DPR rating
DT_bondratings[rating=="BBB-"&rating_type=="DPR",rating_code:=10]
DT_bondratings[rating=="NR"&rating_type=="DPR",rating_code:=NA]
#assign code to SPR rating
DT_bondratings[rating=="A"&rating_type=="SPR",rating_code:=6]
DT_bondratings[rating=="A+"&rating_type=="SPR",rating_code:=5]
DT_bondratings[rating=="A-"&rating_type=="SPR",rating_code:=7]
DT_bondratings[rating=="AA"&rating_type=="SPR",rating_code:=3]
DT_bondratings[rating=="AA+"&rating_type=="SPR",rating_code:=2]
DT_bondratings[rating=="AA-"&rating_type=="SPR",rating_code:=4]
DT_bondratings[rating=="AAA"&rating_type=="SPR",rating_code:=1]
DT_bondratings[rating=="B"&rating_type=="SPR",rating_code:=15]
DT_bondratings[rating=="B+"&rating_type=="SPR",rating_code:=14]
DT_bondratings[rating=="B-"&rating_type=="SPR",rating_code:=16]
DT_bondratings[rating=="BB"&rating_type=="SPR",rating_code:=12]
DT_bondratings[rating=="BB+"&rating_type=="SPR",rating_code:=11]
DT_bondratings[rating=="BB-"&rating_type=="SPR",rating_code:=13]
DT_bondratings[rating=="BBB"&rating_type=="SPR",rating_code:=9]
DT_bondratings[rating=="BBB+"&rating_type=="SPR",rating_code:=8]
DT_bondratings[rating=="BBB-"&rating_type=="SPR",rating_code:=10]
DT_bondratings[rating=="C"&rating_type=="SPR",rating_code:=21]
DT_bondratings[rating=="CC"&rating_type=="SPR",rating_code:=20]
DT_bondratings[rating=="CCC"&rating_type=="SPR",rating_code:=18]
DT_bondratings[rating=="CCC+"&rating_type=="SPR",rating_code:=17]
DT_bondratings[rating=="CCC-"&rating_type=="SPR",rating_code:=19]
DT_bondratings[rating=="D"&rating_type=="SPR",rating_code:=25]
DT_bondratings[rating=="NR"&rating_type=="SPR",rating_code:=NA]
#assign code to MR rating
DT_bondratings[rating=="A"&rating_type=="MR",rating_code:=6]
DT_bondratings[rating=="A1"&rating_type=="MR",rating_code:=5]
DT_bondratings[rating=="A2"&rating_type=="MR",rating_code:=6]
DT_bondratings[rating=="A3"&rating_type=="MR",rating_code:=7]
DT_bondratings[rating=="Aa"&rating_type=="MR",rating_code:=3]
DT_bondratings[rating=="Aa1"&rating_type=="MR",rating_code:=2]
DT_bondratings[rating=="Aa2"&rating_type=="MR",rating_code:=3]
DT_bondratings[rating=="Aa3"&rating_type=="MR",rating_code:=4]
DT_bondratings[rating=="Aaa"&rating_type=="MR",rating_code:=1]
DT_bondratings[rating=="B"&rating_type=="MR",rating_code:=15]
DT_bondratings[rating=="B1"&rating_type=="MR",rating_code:=14]
DT_bondratings[rating=="B2"&rating_type=="MR",rating_code:=15]
DT_bondratings[rating=="B3"&rating_type=="MR",rating_code:=16]
DT_bondratings[rating=="Ba"&rating_type=="MR",rating_code:=12]
DT_bondratings[rating=="Ba1"&rating_type=="MR",rating_code:=11]
DT_bondratings[rating=="Ba2"&rating_type=="MR",rating_code:=12]
DT_bondratings[rating=="Ba3"&rating_type=="MR",rating_code:=13]
DT_bondratings[rating=="Baa"&rating_type=="MR",rating_code:=9]
DT_bondratings[rating=="Baa1"&rating_type=="MR",rating_code:=8]
DT_bondratings[rating=="Baa2"&rating_type=="MR",rating_code:=9]
DT_bondratings[rating=="Baa3"&rating_type=="MR",rating_code:=10]
DT_bondratings[rating=="C"&rating_type=="MR",rating_code:=21]
DT_bondratings[rating=="Ca"&rating_type=="MR",rating_code:=20]
DT_bondratings[rating=="Caa"&rating_type=="MR",rating_code:=18]
DT_bondratings[rating=="Caa1"&rating_type=="MR",rating_code:=17]
DT_bondratings[rating=="Caa2"&rating_type=="MR",rating_code:=18]
DT_bondratings[rating=="Caa3"&rating_type=="MR",rating_code:=19]
DT_bondratings[rating=="NR"&rating_type=="MR",rating_code:=NA]
#regard P-1 as SUSP in handbook
DT_bondratings[rating=="P-1"&rating_type=="MR",rating_code:=NA]
#assign code to FR rating
DT_bondratings[rating=="A"&rating_type=="FR",rating_code:=6]
DT_bondratings[rating=="A+"&rating_type=="FR",rating_code:=5]
DT_bondratings[rating=="A-"&rating_type=="FR",rating_code:=7]
DT_bondratings[rating=="AA"&rating_type=="FR",rating_code:=3]
DT_bondratings[rating=="AA+"&rating_type=="FR",rating_code:=2]
DT_bondratings[rating=="AA-"&rating_type=="FR",rating_code:=4]
DT_bondratings[rating=="AAA"&rating_type=="FR",rating_code:=1]
DT_bondratings[rating=="B"&rating_type=="FR",rating_code:=15]
DT_bondratings[rating=="B+"&rating_type=="FR",rating_code:=14]
DT_bondratings[rating=="B-"&rating_type=="FR",rating_code:=16]
DT_bondratings[rating=="BB"&rating_type=="FR",rating_code:=12]
DT_bondratings[rating=="BB+"&rating_type=="FR",rating_code:=11]
DT_bondratings[rating=="BB-"&rating_type=="FR",rating_code:=13]
DT_bondratings[rating=="BBB"&rating_type=="FR",rating_code:=9]
DT_bondratings[rating=="BBB+"&rating_type=="FR",rating_code:=8]
DT_bondratings[rating=="BBB-"&rating_type=="FR",rating_code:=10]
DT_bondratings[rating=="C"&rating_type=="FR",rating_code:=21]
DT_bondratings[rating=="CC"&rating_type=="FR",rating_code:=20]
DT_bondratings[rating=="CCC"&rating_type=="FR",rating_code:=18]
DT_bondratings[rating=="CCC+"&rating_type=="FR",rating_code:=17]
DT_bondratings[rating=="CCC-"&rating_type=="FR",rating_code:=19]
DT_bondratings[rating=="D"&rating_type=="FR",rating_code:=25]
DT_bondratings[rating=="DD"&rating_type=="FR",rating_code:=24]
DT_bondratings[rating=="DDD"&rating_type=="FR",rating_code:=23]
DT_bondratings[rating=="NR"&rating_type=="FR",rating_code:=NA]

DT_bondratings[,mean_rating:=""]
DT_bondratings[,mean_rating_code:=mean(rating_code,na.rm = TRUE),
               by=c("rating_date",FISD_identifier)]
#assign mean_rating according to mean_rating_code
DT_bondratings[mean_rating_code>5.5&mean_rating_code<=6.5,mean_rating:="A"]
DT_bondratings[mean_rating_code>4.5&mean_rating_code<=5.5,mean_rating:="A+"]
DT_bondratings[mean_rating_code>6.5&mean_rating_code<=7.5,mean_rating:="A-"]
DT_bondratings[mean_rating_code>2.5&mean_rating_code<=3.5,mean_rating:="AA"]
DT_bondratings[mean_rating_code>1.5&mean_rating_code<=2.5,mean_rating:="AA+"]
DT_bondratings[mean_rating_code>3.5&mean_rating_code<=4.5,mean_rating:="AA-"]
DT_bondratings[mean_rating_code<=1.5,mean_rating:="AAA"]
DT_bondratings[mean_rating_code>14.5&mean_rating_code<=15.5,mean_rating:="B"]
DT_bondratings[mean_rating_code>13.5&mean_rating_code<=14.5,mean_rating:="B+"]
DT_bondratings[mean_rating_code>15.5&mean_rating_code<=16.5,mean_rating:="B-"]
DT_bondratings[mean_rating_code>11.5&mean_rating_code<=12.5,mean_rating:="BB"]
DT_bondratings[mean_rating_code>10.5&mean_rating_code<=11.5,mean_rating:="BB+"]
DT_bondratings[mean_rating_code>12.5&mean_rating_code<=13.5,mean_rating:="BB-"]
DT_bondratings[mean_rating_code>8.5&mean_rating_code<=9.5,mean_rating:="BBB"]
DT_bondratings[mean_rating_code>7.5&mean_rating_code<=8.5,mean_rating:="BBB+"]
DT_bondratings[mean_rating_code>9.5&mean_rating_code<=10.5,mean_rating:="BBB-"]
DT_bondratings[mean_rating_code>20.5&mean_rating_code<=21.5,mean_rating:="C"]
DT_bondratings[mean_rating_code>19.5&mean_rating_code<=20.5,mean_rating:="CC"]
DT_bondratings[mean_rating_code>17.5&mean_rating_code<=18.5,mean_rating:="CCC"]
DT_bondratings[mean_rating_code>16.5&mean_rating_code<=17.5,mean_rating:="CCC+"]
DT_bondratings[mean_rating_code>18.5&mean_rating_code<=19.5,mean_rating:="CCC-"]
DT_bondratings[mean_rating_code>24.5&mean_rating_code<=25.5,mean_rating:="D"]
DT_bondratings[is.na(mean_rating_code),mean_rating:="NR"]

#calculate mean investment grade
DT_bondratings[,investment_grade_code:=0]
DT_bondratings[investment_grade=="Y",investment_grade_code:=1]
DT_bondratings[investment_grade=="",investment_grade_code:=NA]
DT_bondratings[,max_investment_grade:=""]
#here at least one agency (on the same date) rates it as investment-grade bond, we regard it investment grade.
DT_bondratings[,max_investmentgrade_code:=max(investment_grade_code,na.rm = TRUE),
               by=c("rating_date",FISD_identifier)]
DT_bondratings[max_investmentgrade_code==1,max_investment_grade:="Y"]
DT_bondratings[max_investment_grade!="Y",`:=`(max_investment_grade="N")]








